A database is a collection of related data. A database management system (DBMS) is a collection of programs that enables users to construct and maintain a database. The DBMS is hence a general-purpose software system that facilitates the processes of defining and constructing databases. Defining a database involves constructing the type of data to be stored in the database, along with a detailed description of each type of data. Constructing the database involves storing the data itself on a storage medium controlled by the DBMS.
Data in a DBMS is typically stored in tables. A table is a series of rows, also called records. Each row contains data about a particular thing, such as an employee. The rows are divided into columns. The intersection of a row and a column is referred to as a field. Each column specifies a particular type of data that is contained in each field of the column. Each field contains the data of the particular type for the intersecting row and column. Example columns for an Employees table include Name, Title, Manager, and CompensationType. A row of such a table would contain the name, title, manager, and compensation type for a particular employee, each in a separate field.
FIG. 1A is a table diagram showing the contents of a sample table named Employees. The Employees table 100 contains 27 rows 101-127. Each row contains a field for each for four columns, a Name column 131, a Title column 132, a Manager column 133, and a Compensation Type 134. For instance, row 101 has a Name field that contains "Andrea", a Title field that contains "Tester", a Manager field that contains "Rose", and a Compensation Type field that contains "Salary."
A database engine is a computer program which includes the capability for retrieving data from the tables of the DBMS. The database engine can either be used directly by a person or used by a database front-end program acting on a person's behalf. An engine typically receives instructions to retrieve data stored in tables, called queries, from a user. Queries are usually expressed in one of the several common query languages, such as structured query language (SQL).
A query specifies the data to be retrieved and the manipulations to be made to the retrieved data. A query, therefore, specifies the table or tables from which data is to be retrieved (the source tables), selection criteria for the rows to be retrieved, the definition of derived fields, and other related information. A derived field is a field that contains data that is derived from one or more other fields.
In response to a query, a conventional (typical prior) engine performs data retrieval and manipulation specified by the query. In doing so, the conventional engine generates a conventional query table to hold the result. The contents of the conventional query table may be examined by using primitive operations defined by the conventional engine. These primitive operations may be used to browse through the query table, that is, step through the rows of the conventional query table and retrieve data that the engine stored in the fields which processing the query. In this way, the user can collect the data comprising the result of the query.
As an example of a query, the user could construct and submit a query to select the salaried employees from the employees table, then use a Managers table to determine each employee's DepartmentNumber based on the name of the employee's manager. FIG. 1B is a table diagram showing the contents of the Managers table. The Managers table 150 contains nine rows 151-159. Each row contains a field for each of two columns: a Manager column 161 and a DepartmentNumber column 162. If expressed in SQL, the query would appear as follows:
1. SELECT Employees.Name, Employees.Manager, PA1 2. Managers.DepartmentNumber PA1 3. FROM Employees, Managers, Employees INNER JOIN Managers PA1 4. ON Employees.Manager=Managers.Manager PA1 5. WHERE Employees.Compensation Type="salary";